{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congestion Charges - Hard\n",
    "\n",
    "You may need to create views to complete these questions - but you do not have permission to create tables or views in the default schema. Your SQL commands are executed by user scott in schema gisq - you may create or drop views and tables in schema scott but not in gisq."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "camera = pd.read_sql_table('camera', engine)\n",
    "keeper = pd.read_sql_table('keeper', engine)\n",
    "vehicle = pd.read_sql_table('vehicle', engine)\n",
    "image = pd.read_sql_table('image', engine)\n",
    "permit = pd.read_sql_table('permit', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "When creating a view in scott you must specify the schema name of the sources and the destination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "scott=pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "There are four types of permit. The most popular type means that this type has been issued the highest number of times. Find out the most popular type, together with the total number of permits issued."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>chargetype</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Daily</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  chargetype  cnt\n",
       "1      Daily   27"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(permit.groupby('chargetype').agg(cnt=pd.NamedAgg(column='reg', aggfunc='count'))\n",
    " .reset_index()\n",
    " .sort_values('cnt', ascending=False).iloc[:1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "For each of the vehicles caught by camera 19 - show the registration, the earliest time at camera 19 and the time and camera at which it left the zone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reg</th>\n",
       "      <th>earliest</th>\n",
       "      <th>next</th>\n",
       "      <th>camera</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SO 02 CSP</td>\n",
       "      <td>2007-02-25 07:51:10</td>\n",
       "      <td>2007-02-25 07:55:11</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SO 02 DSP</td>\n",
       "      <td>2007-02-25 16:31:01</td>\n",
       "      <td>2007-02-25 17:42:41</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SO 02 JSP</td>\n",
       "      <td>2007-02-25 17:14:11</td>\n",
       "      <td>2007-02-25 17:17:03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SO 02 TSP</td>\n",
       "      <td>2007-02-25 07:23:00</td>\n",
       "      <td>2007-02-25 07:26:31</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         reg            earliest                next  camera\n",
       "0  SO 02 CSP 2007-02-25 07:51:10 2007-02-25 07:55:11      18\n",
       "1  SO 02 DSP 2007-02-25 16:31:01 2007-02-25 17:42:41      19\n",
       "2  SO 02 JSP 2007-02-25 17:14:11 2007-02-25 17:17:03       3\n",
       "3  SO 02 TSP 2007-02-25 07:23:00 2007-02-25 07:26:31      19"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  registrations showing at camera 19, the earliest time\n",
    "t = (image.loc[image['camera']==19]\n",
    "     .merge(vehicle, left_on='reg', right_on='id')\n",
    "     .groupby('reg')\n",
    "     .agg(earliest=pd.NamedAgg(column='whn', aggfunc='min'))\n",
    "     .reset_index()[['reg', 'earliest']])\n",
    "a = (t.merge(image, on='reg', how='left')\n",
    "     .query('earliest < whn')\n",
    "     .groupby(['reg', 'earliest'])\n",
    "     .agg(next=pd.NamedAgg(column='whn', aggfunc='min'))\n",
    "     .reset_index()[['reg', 'earliest', 'next']])\n",
    "(a.merge(image, left_on=['reg', 'next'], right_on=['reg', 'whn'])\n",
    " [['reg', 'earliest', 'next', 'camera']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "For all 19 cameras - show the position as IN, OUT or INTERNAL and the busiest hour for that camera."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>camera</th>\n",
       "      <th>type</th>\n",
       "      <th>hr</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>IN</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>IN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>IN</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>IN</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>IN</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>IN</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "      <td>OUT</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9</td>\n",
       "      <td>OUT</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>OUT</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>OUT</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>OUT</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10</td>\n",
       "      <td>OUT</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>11</td>\n",
       "      <td>OUT</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>11</td>\n",
       "      <td>OUT</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>12</td>\n",
       "      <td>OUT</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>12</td>\n",
       "      <td>OUT</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>12</td>\n",
       "      <td>OUT</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>15</td>\n",
       "      <td>OUT</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>16</td>\n",
       "      <td>OUT</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>17</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>17</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>18</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>18</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>18</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>18</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>19</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>19</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>19</td>\n",
       "      <td>INTERNAL</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    camera      type  hr  n\n",
       "0        1        IN   6  1\n",
       "1        2        IN   7  1\n",
       "2        3        IN  17  3\n",
       "3        3        IN  18  2\n",
       "4        5        IN   7  1\n",
       "5        8        IN   7  2\n",
       "6        9       OUT   6  1\n",
       "7        9       OUT  16  6\n",
       "8        9       OUT  18  1\n",
       "9       10       OUT   5  1\n",
       "10      10       OUT   7  1\n",
       "11      10       OUT  18  2\n",
       "13      11       OUT  18  2\n",
       "12      11       OUT   7  1\n",
       "14      12       OUT   7  1\n",
       "15      12       OUT  17  1\n",
       "16      12       OUT  18  2\n",
       "17      15       OUT  18  1\n",
       "18      16       OUT   7  1\n",
       "19      17  INTERNAL   6  2\n",
       "20      17  INTERNAL   7  1\n",
       "21      18  INTERNAL   6  1\n",
       "22      18  INTERNAL   7  3\n",
       "23      18  INTERNAL  16  3\n",
       "24      18  INTERNAL  17  1\n",
       "26      19  INTERNAL  16  1\n",
       "25      19  INTERNAL   7  4\n",
       "27      19  INTERNAL  17  2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (camera.assign(type=camera['perim'].fillna('INTERNAL'))\n",
    "     .merge(image.assign(hr=image['whn'].dt.hour), \n",
    "            left_on='id', right_on='camera')\n",
    "     .groupby(['camera', 'type', 'hr'])\n",
    "     .agg(n=pd.NamedAgg(column='id', aggfunc='count'))\n",
    "     .reset_index())\n",
    "(t.groupby(['camera', 'type', 'hr'])['n'].max()\n",
    " .reset_index().sort_values('camera'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Anomalous daily permits. Daily permits should not be issued for non-charging days. Find a way to represent charging days. Identify the anomalous daily permits."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reg</th>\n",
       "      <th>sdate</th>\n",
       "      <th>chargetype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SO 02 ATP</td>\n",
       "      <td>2007-01-21</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SO 02 BTP</td>\n",
       "      <td>2007-02-03</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SO 02 BTP</td>\n",
       "      <td>2007-02-04</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SO 02 CTP</td>\n",
       "      <td>2007-01-21</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>SO 02 FTP</td>\n",
       "      <td>2007-02-25</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>SO 02 HTP</td>\n",
       "      <td>2006-01-21</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>SO 02 HTP</td>\n",
       "      <td>2006-01-22</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>SO 02 JTP</td>\n",
       "      <td>2007-01-21</td>\n",
       "      <td>Daily</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          reg      sdate chargetype\n",
       "1   SO 02 ATP 2007-01-21      Daily\n",
       "6   SO 02 BTP 2007-02-03      Daily\n",
       "7   SO 02 BTP 2007-02-04      Daily\n",
       "12  SO 02 CTP 2007-01-21      Daily\n",
       "21  SO 02 FTP 2007-02-25      Daily\n",
       "27  SO 02 HTP 2006-01-21      Daily\n",
       "28  SO 02 HTP 2006-01-22      Daily\n",
       "33  SO 02 JTP 2007-01-21      Daily"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "permit.loc[(permit['sdate'].dt.weekday.isin([5, 6])) &\n",
    "           (permit['chargetype']=='Daily')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Issuing fines: Vehicles using the zone during the charge period, on charging days must be issued with fine notices unless they have a permit covering that day. List the name and address of such culprits, give the camera and the date and time of the first offence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reg</th>\n",
       "      <th>name</th>\n",
       "      <th>address</th>\n",
       "      <th>first_offence</th>\n",
       "      <th>camera</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SO 02 ASP</td>\n",
       "      <td>Ambiguous, Arthur</td>\n",
       "      <td>Absorption Ave.</td>\n",
       "      <td>2007-02-25 06:10:13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SO 02 CSP</td>\n",
       "      <td>Ambiguous, Arthur</td>\n",
       "      <td>Absorption Ave.</td>\n",
       "      <td>2007-02-25 06:57:31</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SO 02 DSP</td>\n",
       "      <td>Strenuous, Sam</td>\n",
       "      <td>Surjection Street</td>\n",
       "      <td>2007-02-25 16:29:11</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SO 02 GSP</td>\n",
       "      <td>Incongruous, Ingrid</td>\n",
       "      <td>Irresolution Pl.</td>\n",
       "      <td>2007-02-25 07:10:00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SO 02 HSP</td>\n",
       "      <td>Assiduous, Annie</td>\n",
       "      <td>Attribution Alley</td>\n",
       "      <td>2007-02-25 16:45:04</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SO 02 ISP</td>\n",
       "      <td>Incongruous, Ingrid</td>\n",
       "      <td>Irresolution Pl.</td>\n",
       "      <td>2007-02-25 16:58:01</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SO 02 JSP</td>\n",
       "      <td>Inconspicuous, Iain</td>\n",
       "      <td>Interception Rd.</td>\n",
       "      <td>2007-02-25 17:07:00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         reg                 name            address       first_offence  \\\n",
       "0  SO 02 ASP    Ambiguous, Arthur    Absorption Ave. 2007-02-25 06:10:13   \n",
       "1  SO 02 CSP    Ambiguous, Arthur    Absorption Ave. 2007-02-25 06:57:31   \n",
       "2  SO 02 DSP       Strenuous, Sam  Surjection Street 2007-02-25 16:29:11   \n",
       "3  SO 02 GSP  Incongruous, Ingrid   Irresolution Pl. 2007-02-25 07:10:00   \n",
       "4  SO 02 HSP     Assiduous, Annie  Attribution Alley 2007-02-25 16:45:04   \n",
       "5  SO 02 ISP  Incongruous, Ingrid   Irresolution Pl. 2007-02-25 16:58:01   \n",
       "6  SO 02 JSP  Inconspicuous, Iain   Interception Rd. 2007-02-25 17:07:00   \n",
       "\n",
       "   camera  \n",
       "0       1  \n",
       "1      17  \n",
       "2      18  \n",
       "3       5  \n",
       "4       9  \n",
       "5       9  \n",
       "6       3  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas.tseries.offsets import DateOffset\n",
    "\n",
    "# vehicles with permits\n",
    "t = (vehicle.merge(permit, left_on='id', right_on='reg', how='left')\n",
    "     [['reg', 'sdate', 'keeper', 'chargetype']]\n",
    "    .assign(edate=permit['sdate']))\n",
    "t.loc[t['chargetype']=='Daily', 'edate'] = t.loc[\n",
    "    t['chargetype']=='Daily', 'sdate'] + DateOffset(days=1)\n",
    "t.loc[t['chargetype']=='Weekly', 'edate'] = t.loc[\n",
    "    t['chargetype']=='Weekly', 'sdate'] + DateOffset(weeks=1)\n",
    "t.loc[t['chargetype']=='Monthly', 'edate'] = t.loc[\n",
    "    t['chargetype']=='Monthly', 'sdate'] + DateOffset(months=1)\n",
    "t.loc[t['chargetype']=='Annual', 'edate'] = t.loc[\n",
    "    t['chargetype']=='Annual', 'sdate'] + DateOffset(years=1)\n",
    "\n",
    "f = (t.merge(image, on='reg', how='right')\n",
    "     .merge(keeper.rename(columns={'id': 'keeper'}), on='keeper'))\n",
    "f = f.loc[(f['whn']<f['sdate']) | (f['whn']>f['edate'])]\n",
    "\n",
    "a = (f.groupby(['reg', 'name', 'address'])\n",
    "     .agg(first_offence=pd.NamedAgg(column='whn', aggfunc='min'))\n",
    "     .reset_index())\n",
    "(a.merge(f[['reg', 'whn', 'camera']], \n",
    "         left_on=['reg', 'first_offence'], right_on=['reg', 'whn'])\n",
    " [['reg', 'name', 'address', 'first_offence', 'camera']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
